package com.lanqiao.gxnushop.mapper;

import com.lanqiao.gxnushop.util.StringUtil;
import com.lanqiao.gxnushop.valueObject.Financial;

public class FinancialRecordsMapperProvide {
    public String getListSql(Financial financial){
        StringBuffer sb = new StringBuffer("SELECT s.sellerId,st.storeId,st.name AS storeName,MONTH(o.zcsj) AS month,COALESCE(SUM(o.totalAmount), 0) AS record,count(*) as num \n" +
                "FROM Sellers s\n" +
                "LEFT JOIN Stores st ON s.sellerId = st.sellerId\n" +
                "LEFT JOIN Orders o ON st.storeId = o.storeId  \n" +
                " and (CASE\n" +
                "WHEN #{num} = 3 THEN o.zcsj >= CURDATE() - INTERVAL 3 MONTH\n" +
                "WHEN #{num} = 6 THEN o.zcsj >= CURDATE() - INTERVAL 6 MONTH\n" +
                "WHEN #{num} = 12 THEN o.zcsj >= CURDATE() - INTERVAL 12 MONTH\n" +
                "ELSE true\n" +
                "END)\n"+
                " WHERE s.sellerId = #{sellerId} ");
        if(StringUtil.isNotEmpty(financial.getStoreName())&&financial.getStoreName()!=""){
            sb.append(" and st.name like concat('%',#{storeName},'%')");
        }
        sb.append(" GROUP BY s.sellerId, st.storeId, st.name, MONTH(o.zcsj)");
        String sql = sb.toString();
        return sql;
    }
}
